IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDelegateList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetDelegateList]
GO
 
SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************              
* Name:     [GetDelegateList]              
* Purpose:  Get the details of the delegate Ids passed in               
* PARAMETERS(IN)              
* Name          Description                   
* -------------      -------------------------------------------              
* @xml       xml Document containing the hierarchy(REQUIRED)        
 The passed in XML has the following form:      
  <NewDataSet>      
   <Table>      
    <ParentId>234711</ParentId>   
 <ChildId>78543</ChildId>  
 <Removed>0</Removed>    
    </Table>      
   <Table>      
    <ParentId>185678</ParentId>   
 <ChildId>67543</ChildId>  
 <Removed>0</Removed>      
   </Table>      
   ...      
  </NewDataSet>  
                 
*********************************************************************/  
CREATE PROCEDURE dbo.[GetDelegateList]  
(  
 @xml text  
)  
AS BEGIN  
SET NOCOUNT ON  
  
 declare @xmlDoc int                    
 declare @delegatePersonIds table ([DelegateId] int)                    
 exec sp_xml_preparedocument @xmlDoc output, @xml                    
 insert into @delegatePersonIds select [Id] from openxml(@xmlDoc, '//IdList', 2) with (Id int)                    
 exec sp_xml_removedocument @xmlDoc                  
                  
Select DelegateId ,  
    fdo.fullyQualifiedName as Organization,  
    adp.LastName +', '+ adp.FirstName as [Name],  
    fdu.UserName   
 from @delegatePersonIds dpi  
 inner join AdmPerson adp  
 on dpi.DelegateId = adp.Id  
 Left join FwkDomainUser fdu  
 on fdu.Id = adp.FwkDomainUserId  
 LEFT JOIN AdmInternalPerson aip  
 on aip.AdmPersonId = adp.Id  
 Left JOIN FwkDomainOrganization fdo  
 on fdo.Id = aip.PrimaryOrganizationId    
END  